using System;
using System.Data;
using System.Data.SqlClient;
using DTN.Core.Common;
namespace com.dtn.deals
{
	public class CommentDB
	{
		public static DataTable GetAll ()  
		{
 			DataTable retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
 			SqlCommand dbCmd = new SqlCommand("Comments_GetAll", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			try
			{
				retVal = new DataTable() ;
				SqlDataAdapter da = new SqlDataAdapter(dbCmd);
				da.Fill(retVal);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal; 
		}
		public static void Delete (int id)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Comments_Delete", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.Add("@Id",id);
			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}
		public static int Insert (CommentInfo CommentInfo)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Comments_Insert", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
 					dbCmd.Parameters.Add("@ObjId",CommentInfo.ObjId);
					dbCmd.Parameters.Add("@UserId",CommentInfo.UserId);
					dbCmd.Parameters.Add("@Email",CommentInfo.Email);
					dbCmd.Parameters.Add("@NickName",CommentInfo.NickName);
					dbCmd.Parameters.Add("@Comment",CommentInfo.Comment);
			dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
			try {
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static void Update (CommentInfo CommentInfo)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Comments_Update", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
					dbCmd.Parameters.Add("@Id",CommentInfo.Id);
					dbCmd.Parameters.Add("@ObjId",CommentInfo.ObjId);
					dbCmd.Parameters.Add("@UserId",CommentInfo.UserId);
					dbCmd.Parameters.Add("@Email",CommentInfo.Email);
					dbCmd.Parameters.Add("@NickName",CommentInfo.NickName);
					dbCmd.Parameters.Add("@Comment",CommentInfo.Comment);
			try {
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}
 
		public static CommentInfo GetInfo ( int id )
		{
			CommentInfo retVal = null;
 			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Comments_GetInfo", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.Add("@Id", id);
			try 
			{
				dbConn.Open();
				SqlDataReader dr = dbCmd.ExecuteReader();
				if (dr.Read()) retVal = new CommentInfo(dr);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal;
		}
        public static DataTable GetAndPaging(int objId, int _pageSize,
                                                      int _currentPage, out int _totalRecord)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("Comments_GetAndPaging", dbConn);
            dbCmd.Parameters.AddWithValue("@ObjId", objId);
            dbCmd.Parameters.AddWithValue("@PageSize", _pageSize);
            dbCmd.Parameters.AddWithValue("@PageIndex", _currentPage);
            dbCmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
            dbCmd.Parameters["@Count"].Direction = ParameterDirection.Output;
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                _totalRecord = Convert.ToInt32(dbCmd.Parameters["@Count"].Value);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
	}
}

